*-----------------------------------------------------------------
cap clear
cap clear mata
capture log close
program drop _all
macro drop _all
version 11
set mem 740m
set matsize 900 
set more off

cd "E:\REStat_MS14767_Vol96(2)\Data preparation Compustat segment"
log using "7_RJVparticip.log", replace
*-----------------------------------------------------------------

************************************************************
* This file is very important! It uses the RJV panel
* and creates variables!
***********************************************************


use "raw_rjv_panel_segment.dta", clear


*************
* total number of insiders in a RJV/year
*************

egen nfirms=sum(ins), by (rjvnum year)
label var nfirms "Total number of insiders in a RJV/year"


*************
* we have to correct the nonprofit variable because of a clear mistake: a nonprofit cannot have a ticker!
*************

replace nonprofit=0 if ticker!="" 
gen nonprof=nonprofit
replace nonprof=0 if ins==0 

egen nfirmsNP=sum(nonprof), by (rjvnum year)
label var nfirmsNP "Total number of non-profit insiders in a RJV/year"

gen nfirmsP=nfirms-nfirmsNP
label var nfirmsP "Total number of for profit insiders in a RJV/year"

*br rjvnum year comnum  eyear xyear ins nfirms  nfirmsP nfirmsNP nonprofit


*************
* here we retrive some relevant information (SIC, market shares, HHI) of the firms from compustat
*************

merge m:1  ticker year using "segment_wide.dta"
tab _merge
drop if _merge==2
count if _merge==1 & ticker!=""


label var sic4 "RJV specific sic4 code"


*************
* generate the number of firms in a RJV/year that are in compustat
* generate the number of insiders from the same SIC4 industry in a RJV/year
*************

gen insCOMP=ins 
replace insCOMP=0 if ticker=="" 
replace insCOMP=0 if _merge==1
egen nfirmsCOMP=sum(insCOMP), by(rjvnum year)
label var nfirmsCOMP "Total number of COMPUSTAT insiders in a RJV/year"


*************
* here we have to reshape the data long, since for some firms we have 
* several SIC-Codes  
*************


reshape long SIC MS, i(rjvnum ticker year) j(seq_sic)
drop if seq_sic != 1 & _merge ==1
drop if SIC ==. & _merge ==3
egen nfirmsPsame=sum(insCOMP), by(rjvnum year SIC)
label var nfirmsPsame "Total number of insiders in a RJV/year from same SIC4"

drop _merge

egen comnum2=group(entityname)
drop comnum
rename comnum2 comnum


*****************************************
* here we count the number of "unique" firms in a SIC4 connected through a RJV
*****************************************

* here we count the number of "unique" firms in a SIC4 connected through a RJV
*****************************************

*Number of firms in a  RJV, which are in the same SIC in a certein Year:
egen x1=count(comnum) if SIC !=. , by(year SIC rjvnum)
egen x2 = max(x1), by(year SIC comnum) 

* Number of unique firms in the SIC-Code in a certain year
egen se = seq(), by(year SIC comnum)
egen x3=count(comnum) if se ==1 & SIC !=., by(year SIC)
*egen x3 = max(x2), by(year SIC) 
* Those which are alone in an RJV do not form a link, thus
*they can not be counted into the total number of links in the 
* SIC through RJVs:
replace x3=. if x2<=1 

egen links_tot=max(x3), by (year SIC comnum)
label var links_tot "Total number of links in the SIC4 through RJVs"

egen zz=sum(x1) if SIC !=., by(year SIC comnum)
egen z2=count(comnum), by (year SIC comnum)
gen links_dir=zz-z2
label var links_dir "Total number of direct links in the SIC4 through RJVs"
drop x1 se x2 x3 zz z2


gen x1=0
egen se = seq(), by(year SIC comnum)
replace x1=1 if se == 1
egen MStot0=sum(MS) if x1 == 1, by(year SIC x1)
egen MStot = max(MStot0), by(year SIC)
replace MStot = . if MS == .

replace MStot=0 if links_tot==0
drop MStot0 se x1
label var MStot "Market share of firms participating in  RJVs in that SIC "



**************************************************************
/*
reshape wide SIC MS, i(rjvnum ticker year) j(seq_sic)
sort  year ticker
count if ticker[_n] == ticker[_n-1]
*0
*/

**************************************************************
* It has been checked that the maximum number of rjvs where one firm is in in this dataset is 178.
*
egen xxx=count(ins), by (comnum year SIC)
sum xxx
* 

*
drop xxx
******************************************************************



*************
* generate the variable rjv_`i', in_`i', nrjv`i', 
*************

scalar nr_rjv = r(max)


sort year comnum SIC rjvnum

local fin = nr_rjv
local i=1
forvalues i=1/`fin'{
gen rjv`i'="."
destring rjv`i', replace
}


local fin = nr_rjv
local i=1
forvalues i=1/`fin'{
gen in`i'="."
destring in`i', replace
}


local fin = nr_rjv
local i=1
forvalues i=1/`fin'{
gen nrjv`i'="."
destring nrjv`i', replace
}


local fin = nr_rjv
local i=1
forvalues i=1/`fin'{
gen nrjvCOMP`i'="."
destring nrjvCOMP`i', replace
}


local fin = nr_rjv
local i=1
forvalues i=1/`fin'{
gen nrjvsame`i'="."
destring nrjvsame`i', replace
}

egen obsnum=seq(), by(year comnum SIC)
sum obsnum
*************
* It has been checked that the number of firms in this dataset is 1095.
* sum comnum
*************


sum comnum

so year comnum rjvnum

*************
* edit the variable rjv_`i', in_`i', nrjv`i', 
*************

local nr_com= r(max)
local j = 1
forvalues j =1/`nr_com'{
	local fin = nr_rjv
	local i=1
		forvalues i=1/`fin'{
		by year: replace rjv`i'=rjvnum if (comnum==`j' & obsnum==`i')
		by year: replace in`i'=ins if (comnum==`j' & obsnum==`i')
		by year: replace nrjv`i'=nfirmsP if (comnum==`j' & obsnum==`i')
		by year: replace nrjvCOMP`i'=nfirmsCOMP if (comnum==`j' & obsnum==`i')
		by year: replace nrjvsame`i'=nfirmsPsame if (comnum==`j' & obsnum==`i')
		}
}

local fin = nr_rjv
local i=1
forvalues i=1/`fin'{
egen a`i'=max(rjv`i'), by(year comnum)
replace rjv`i'=a`i'
drop a`i'
egen b`i'=max(in`i'), by(year comnum)
replace in`i'=b`i'
drop b`i'
egen c`i'=max(nrjv`i'), by(year comnum)
replace nrjv`i'=c`i'
drop c`i'
egen d`i'=max(nrjvCOMP`i'), by(year comnum)
replace nrjvCOMP`i'=d`i'
drop d`i'
egen e`i'=max(nrjvsame`i'), by(year comnum SIC) /* This had to be changed for the segment data, since the nrfirmsPsame is not constan within firm*/
replace nrjvsame`i'=e`i'
drop e`i'
}

*************
* assign each joint venture its four digit sic code, thus creating 178 new variables
*************
destring sic4, replace
sort  year comnum obsnum


local fin = nr_rjv
local i=1
forvalues i=1/`fin'{
gen rjv_sic`i'="."
destring rjv_sic`i', replace
replace rjv_sic`i'=sic4 if obsnum==`i'
egen a`i'=max(rjv_sic`i'), by(comnum)
replace rjv_sic`i'=a`i'
drop a`i'
}
*************
* data are rearranged here
*************

compress

/*

order entityname rjvnum ticker year sic4 rjvname entryname ///
jv_year eyear xyear  nonprofit foreign1 foreign ins nfirms nonprof ///
nfirmsNP nfirmsP SIC4 MS HHI insCOMP nfirmsCOMP nfirmsPsame comnum ///
rjv1 rjv2 rjv3 rjv4 rjv5 rjv6 rjv7 rjv8 rjv9 ///
rjv10 rjv11 rjv12 rjv13 rjv14 rjv15 rjv16 rjv17 rjv18 rjv19 ///
rjv20 rjv21 rjv22 rjv23 rjv24 rjv25 rjv26 rjv27 rjv28 rjv29 ///
rjv30 rjv31 rjv32 rjv33 rjv34 rjv35 rjv36 rjv37 rjv38 rjv39 ///
rjv40 rjv41 rjv42 rjv43 rjv44 rjv45 rjv46 rjv47 rjv48 rjv49 ///
rjv50 rjv51 rjv52 rjv53 rjv54 rjv55 rjv56 rjv57 rjv58 rjv59 ///
rjv60 rjv61 rjv62 rjv63 rjv64 rjv65 rjv66 rjv67 rjv68 rjv69 ///
rjv70 rjv71 rjv72 rjv73 rjv74 rjv75 rjv76 rjv77 rjv78 rjv79 ///
rjv80 rjv81 rjv82 rjv83 rjv84 rjv85 rjv86 rjv87 rjv88 rjv89 ///
rjv90 rjv91 rjv92 rjv93 rjv94 rjv95 rjv96 rjv97 rjv98 rjv99 ///
rjv100 rjv101 rjv102 rjv103 rjv104 rjv105 rjv106 rjv107 rjv108 rjv109 ///
rjv110 rjv111 rjv112 rjv113 rjv114 rjv115 rjv116 rjv117 rjv118 rjv119 ///
rjv120 rjv121 rjv122 rjv123 rjv124 rjv125 rjv126 rjv127 rjv128 rjv129 ///
rjv130 rjv131 rjv132 rjv133 rjv134 rjv135 rjv136 rjv137 rjv138 rjv139 ///
rjv140 rjv141 rjv142 rjv143 rjv144 rjv145 rjv146 rjv147 rjv148 rjv149 ///
rjv150 rjv151 rjv152 rjv153 rjv154 rjv155 rjv156 rjv157 rjv158 rjv159 ///
rjv160 rjv161 rjv162 rjv163 rjv164 rjv165 rjv166 rjv167 rjv168 rjv169 ///
rjv170 rjv171 rjv172 rjv173 rjv174 rjv175 rjv176 rjv177 rjv178 ///
in1 in2 in3 in4 in5 in6 in7 in8 in9 ///
in10 in11 in12 in13 in14 in15 in16 in17 in18 in19 ///
in20 in21 in22 in23 in24 in25 in26 in27 in28 in29 ///
in30 in31 in32 in33 in34 in35 in36 in37 in38 in39 ///
in40 in41 in42 in43 in44 in45 in46 in47 in48 in49 ///
in50 in51 in52 in53 in54 in55 in56 in57 in58 in59 ///
in60 in61 in62 in63 in64 in65 in66 in67 in68 in69 ///
in70 in71 in72 in73 in74 in75 in76 in77 in78 in79 ///
in80 in81 in82 in83 in84 in85 in86 in87 in88 in89 ///
in90 in91 in92 in93 in94 in95 in96 in97 in98 in99 ///
in100 in101 in102 in103 in104 in105 in106 in107 in108 in109 ///
in110 in111 in112 in113 in114 in115 in116 in117 in118 in119 ///
in120 in121 in122 in123 in124 in125 in126 in127 in128 in129 ///
in130 in131 in132 in133 in134 in135 in136 in137 in138 in139 ///
in140 in141 in142 in143 in144 in145 in146 in147 in148 in149 ///
in150 in151 in152 in153 in154 in155 in156 in157 in158 in159 ///
in160 in161 in162 in163 in164 in165 in166 in167 in168 in169 ///
in170 in171 in172 in173 in174 in175 in176 in177 in178 ///
nrjv1 nrjv2 nrjv3 nrjv4 nrjv5 nrjv6 nrjv7 nrjv8 nrjv9 ///
nrjv10 nrjv11 nrjv12 nrjv13 nrjv14 nrjv15 nrjv16 nrjv17 nrjv18 nrjv19 ///
nrjv20 nrjv21 nrjv22 nrjv23 nrjv24 nrjv25 nrjv26 nrjv27 nrjv28 nrjv29 ///
nrjv30 nrjv31 nrjv32 nrjv33 nrjv34 nrjv35 nrjv36 nrjv37 nrjv38 nrjv39 ///
nrjv40 nrjv41 nrjv42 nrjv43 nrjv44 nrjv45 nrjv46 nrjv47 nrjv48 nrjv49 ///
nrjv50 nrjv51 nrjv52 nrjv53 nrjv54 nrjv55 nrjv56 nrjv57 nrjv58 nrjv59 ///
nrjv60 nrjv61 nrjv62 nrjv63 nrjv64 nrjv65 nrjv66 nrjv67 nrjv68 nrjv69 ///
nrjv70 nrjv71 nrjv72 nrjv73 nrjv74 nrjv75 nrjv76 nrjv77 nrjv78 nrjv79 ///
nrjv80 nrjv81 nrjv82 nrjv83 nrjv84 nrjv85 nrjv86 nrjv87 nrjv88 nrjv89 ///
nrjv90 nrjv91 nrjv92 nrjv93 nrjv94 nrjv95 nrjv96 nrjv97 nrjv98 nrjv99 ///
nrjv100 nrjv101 nrjv102 nrjv103 nrjv104 nrjv105 nrjv106 nrjv107 nrjv108 nrjv109 ///
nrjv110 nrjv111 nrjv112 nrjv113 nrjv114 nrjv115 nrjv116 nrjv117 nrjv118 nrjv119 ///
nrjv120 nrjv121 nrjv122 nrjv123 nrjv124 nrjv125 nrjv126 nrjv127 nrjv128 nrjv129 ///
nrjv130 nrjv131 nrjv132 nrjv133 nrjv134 nrjv135 nrjv136 nrjv137 nrjv138 nrjv139 ///
nrjv140 nrjv141 nrjv142 nrjv143 nrjv144 nrjv145 nrjv146 nrjv147 nrjv148 nrjv149 ///
nrjv150 nrjv151 nrjv152 nrjv153 nrjv154 nrjv155 nrjv156 nrjv157 nrjv158 nrjv159 ///
nrjv160 nrjv161 nrjv162 nrjv163 nrjv164 nrjv165 nrjv166 nrjv167 nrjv168 nrjv169 ///
nrjv170 nrjv171 nrjv172 nrjv173 nrjv174 nrjv175 nrjv176 nrjv177 nrjv178 ///
nrjvCOMP1 nrjvCOMP2 nrjvCOMP3 nrjvCOMP4 nrjvCOMP5 nrjvCOMP6 nrjvCOMP7 nrjvCOMP8 nrjvCOMP9 ///
nrjvCOMP10 nrjvCOMP11 nrjvCOMP12 nrjvCOMP13 nrjvCOMP14 nrjvCOMP15 nrjvCOMP16 nrjvCOMP17 nrjvCOMP18 nrjvCOMP19 ///
nrjvCOMP20 nrjvCOMP21 nrjvCOMP22 nrjvCOMP23 nrjvCOMP24 nrjvCOMP25 nrjvCOMP26 nrjvCOMP27 nrjvCOMP28 nrjvCOMP29 ///
nrjvCOMP30 nrjvCOMP31 nrjvCOMP32 nrjvCOMP33 nrjvCOMP34 nrjvCOMP35 nrjvCOMP36 nrjvCOMP37 nrjvCOMP38 nrjvCOMP39 ///
nrjvCOMP40 nrjvCOMP41 nrjvCOMP42 nrjvCOMP43 nrjvCOMP44 nrjvCOMP45 nrjvCOMP46 nrjvCOMP47 nrjvCOMP48 nrjvCOMP49 ///
nrjvCOMP50 nrjvCOMP51 nrjvCOMP52 nrjvCOMP53 nrjvCOMP54 nrjvCOMP55 nrjvCOMP56 nrjvCOMP57 nrjvCOMP58 nrjvCOMP59 ///
nrjvCOMP60 nrjvCOMP61 nrjvCOMP62 nrjvCOMP63 nrjvCOMP64 nrjvCOMP65 nrjvCOMP66 nrjvCOMP67 nrjvCOMP68 nrjvCOMP69 ///
nrjvCOMP70 nrjvCOMP71 nrjvCOMP72 nrjvCOMP73 nrjvCOMP74 nrjvCOMP75 nrjvCOMP76 nrjvCOMP77 nrjvCOMP78 nrjvCOMP79 ///
nrjvCOMP80 nrjvCOMP81 nrjvCOMP82 nrjvCOMP83 nrjvCOMP84 nrjvCOMP85 nrjvCOMP86 nrjvCOMP87 nrjvCOMP88 nrjvCOMP89 ///
nrjvCOMP90 nrjvCOMP91 nrjvCOMP92 nrjvCOMP93 nrjvCOMP94 nrjvCOMP95 nrjvCOMP96 nrjvCOMP97 nrjvCOMP98 ///
nrjvCOMP100 nrjvCOMP101 nrjvCOMP102 nrjvCOMP103 nrjvCOMP104 nrjvCOMP105 nrjvCOMP106 nrjvCOMP107 nrjvCOMP108 nrjvCOMP109 ///
nrjvCOMP110 nrjvCOMP111 nrjvCOMP112 nrjvCOMP113 nrjvCOMP114 nrjvCOMP115 nrjvCOMP116 nrjvCOMP117 nrjvCOMP118 nrjvCOMP119 ///
nrjvCOMP120 nrjvCOMP121 nrjvCOMP122 nrjvCOMP123 nrjvCOMP124 nrjvCOMP125 nrjvCOMP126 nrjvCOMP127 nrjvCOMP128 nrjvCOMP129 ///
nrjvCOMP130 nrjvCOMP131 nrjvCOMP132 nrjvCOMP133 nrjvCOMP134 nrjvCOMP135 nrjvCOMP136 nrjvCOMP137 nrjvCOMP138 nrjvCOMP139 ///
nrjvCOMP140 nrjvCOMP141 nrjvCOMP142 nrjvCOMP143 nrjvCOMP144 nrjvCOMP145 nrjvCOMP146 nrjvCOMP147 nrjvCOMP148 nrjvCOMP149 ///
nrjvCOMP150 nrjvCOMP151 nrjvCOMP152 nrjvCOMP153 nrjvCOMP154 nrjvCOMP155 nrjvCOMP156 nrjvCOMP157 nrjvCOMP158 nrjvCOMP159 ///
nrjvCOMP160 nrjvCOMP161 nrjvCOMP162 nrjvCOMP163 nrjvCOMP164 nrjvCOMP165 nrjvCOMP166 nrjvCOMP167 nrjvCOMP168 nrjvCOMP169 ///
nrjvCOMP170 nrjvCOMP171 nrjvCOMP172 nrjvCOMP173 nrjvCOMP174 nrjvCOMP175 nrjvCOMP176 nrjvCOMP177 nrjvCOMP178 ///
nrjvsame1 nrjvsame2 nrjvsame3 nrjvsame4 nrjvsame5 nrjvsame6 nrjvsame7 nrjvsame8 nrjvsame9 ///
nrjvsame10 nrjvsame11 nrjvsame12 nrjvsame13 nrjvsame14 nrjvsame15 nrjvsame16 nrjvsame17 nrjvsame18 nrjvsame19 ///
nrjvsame20 nrjvsame21 nrjvsame22 nrjvsame23 nrjvsame24 nrjvsame25 nrjvsame26 nrjvsame27 nrjvsame28 nrjvsame29 ///
nrjvsame30 nrjvsame31 nrjvsame32 nrjvsame33 nrjvsame34 nrjvsame35 nrjvsame36 nrjvsame37 nrjvsame38 nrjvsame39 ///
nrjvsame40 nrjvsame41 nrjvsame42 nrjvsame43 nrjvsame44 nrjvsame45 nrjvsame46 nrjvsame47 nrjvsame48 nrjvsame49 ///
nrjvsame50 nrjvsame51 nrjvsame52 nrjvsame53 nrjvsame54 nrjvsame55 nrjvsame56 nrjvsame57 nrjvsame58 nrjvsame59 ///
nrjvsame60 nrjvsame61 nrjvsame62 nrjvsame63 nrjvsame64 nrjvsame65 nrjvsame66 nrjvsame67 nrjvsame68 nrjvsame69 ///
nrjvsame70 nrjvsame71 nrjvsame72 nrjvsame73 nrjvsame74 nrjvsame75 nrjvsame76 nrjvsame77 nrjvsame78 nrjvsame79 ///
nrjvsame80 nrjvsame81 nrjvsame82 nrjvsame83 nrjvsame84 nrjvsame85 nrjvsame86 nrjvsame87 nrjvsame88 nrjvsame89 ///
nrjvsame90 nrjvsame91 nrjvsame92 nrjvsame93 nrjvsame94 nrjvsame95 nrjvsame96 nrjvsame97 nrjvsame98 nrjvsame99 ///
nrjvsame100 nrjvsame101 nrjvsame102 nrjvsame103 nrjvsame104 nrjvsame105 nrjvsame106 nrjvsame107 nrjvsame108 nrjvsame109 ///
nrjvsame110 nrjvsame111 nrjvsame112 nrjvsame113 nrjvsame114 nrjvsame115 nrjvsame116 nrjvsame117 nrjvsame118 nrjvsame119 ///
nrjvsame120 nrjvsame121 nrjvsame122 nrjvsame123 nrjvsame124 nrjvsame125 nrjvsame126 nrjvsame127 nrjvsame128 nrjvsame129 ///
nrjvsame130 nrjvsame131 nrjvsame132 nrjvsame133 nrjvsame134 nrjvsame135 nrjvsame136 nrjvsame137 nrjvsame138 nrjvsame139 ///
nrjvsame140 nrjvsame141 nrjvsame142 nrjvsame143 nrjvsame144 nrjvsame145 nrjvsame146 nrjvsame147 nrjvsame148 nrjvsame149 ///
nrjvsame150 nrjvsame151 nrjvsame152 nrjvsame153 nrjvsame154 nrjvsame155 nrjvsame156 nrjvsame157 nrjvsame158 nrjvsame159 ///
nrjvsame160 nrjvsame161 nrjvsame162 nrjvsame163 nrjvsame164 nrjvsame165 nrjvsame166 nrjvsame167 nrjvsame168 nrjvsame169 ///
nrjvsame170 nrjvsame171 nrjvsame172 nrjvsame173 nrjvsame174 nrjvsame175 nrjvsame176 nrjvsame177 nrjvsame178 

*/

*************
* Reduce the blown up data to one row per company/SIC and year.
* These commands finally create the rjv-panel.
*************

so comnum year SIC
egen s = seq(), by(year comnum SIC)
count if s !=1
keep if s == 1
drop s
drop rjvname entryname jv_year eyear xyear obsnum  




order ticker year  SIC 

save "RJVparticip_panel.dta", replace

use "RJVparticip_panel.dta", clear

***************************************************
* here we generate the RJVs' participation variables
***************************************************

so ticker year

* we have to drop these vars to have more space
drop rjv1-rjv134

***************************************************
* total numer of RJVs
***************************************************

egen RJVtotal=rowtotal(in*)
label var RJVtotal "Number of RJVs in which the firm participates in in that year"

***************************************************
* Any RJV or no RJV (dummy)
***************************************************

gen RJV=0
replace RJV=1 if RJVtotal>0
label var RJV "Dummy =1 if the firm is at least in one RJV in that year"


***************************************************
* total numer of RJVs with competitors (RJVsametot) and without competitors (RJVdifftot)
***************************************************

local fin = nr_rjv
local i=1
forvalues i=1/`fin'{
qui gen xx`i'=.
qui replace xx`i'=0 if in`i'==1 | in`i'==0
qui replace xx`i'=1 if nrjvsame`i'>1 & nrjvsame`i'!=. & in`i'==1
	}

	
egen RJVsametot=rowtotal(xx*)
label var RJVsametot "Number of RJVs with competitors in which the firm participates in in that year and Industry"
drop xx*

gen RJVdifftot=RJVtotal-RJVsametot
label var RJVdifftot "Number of RJVs without competitors in which the firm participates in in that year and Industry"



***************************************************
* Dummy=1 if the firms was in any RJV with competitiors (same) or without competitors (diff)
***************************************************

gen RJVsame=0
replace RJVsame=1 if RJVsametot>0
label var RJVsame "Dummy =1 if the firm is at least in one RJV with competitors in that year"

gen RJVdiff=0
replace RJVdiff=1 if RJV==1 & RJVsame==0
label var RJVdiff "Dummy =1 if the firm is at least in one RJV without competitors in that year"

***************************************************
* Number of firms in the RJVs
***************************************************

local fin = nr_rjv
local i=1
forvalues i=1/`fin'{
qui gen xx`i'=nrjv`i'*in`i'
	}

egen yy=rowtotal(xx*)
gen RJVnfirms=yy-RJVtotal
label var RJVnfirms "Number of firms in the RJVs where the firm paricipates"
drop xx* yy

*br ticker year in1-in5 nrjv1-nrjv5 RJVnfi if in1!=.

***************************************************
* Number of firms from the same SIC4 in the RJVs (RJVnsame) and from different SIC4 (RJVndiff)
***************************************************

local fin = nr_rjv
local i=1
forvalues i=1/`fin'{
qui gen xx`i'=nrjvsame`i'*in`i'
	}

egen yy=rowtotal(xx*)
gen RJVnsame=yy-RJVsametot
label var RJVnsame "Number of firms from the same SIC4 in the RJVs where the firm paricipates"
drop xx* yy

gen RJVndiff=RJVnfirms-RJVnsame
label var RJVndiff "Number of firms from a different SIC4 in the RJVs where the firm paricipates"

*br ticker year in1-in5 nrjv1-nrjv5 RJVn* if in1!=.


/*
***************************************************
* Percentage of number of firms from the same SIC4 
* in the RJVs on the total number of compustat firms
***************************************************

foreach num of numlist 1/178 {
qui gen xx`num'=(nrjvsame`num'/nrjvCOMP`num')*in`num'
	}

egen yy=rowtotal(xx1-xx178)

RJVnsame_per
label var RJVnsame "Percentage Number of firms from the same SIC4 in the RJVs where the firm paricipates"
drop xx*

br ticker year in1-in5 nrjv1-nrjv5 RJVn* if in1!=.

*/




/*
* total number of RJVs in the same/different SIC4 industry (definition based on primary SIC code)
foreach num of numlist 1/178 {
gen ins`num'=in`num'
replace ins`num'=. if rjv_sic`num'!=SIC4
	}

egen RJVsame=rowtotal(ins1-ins178)
label var RJVsame "Number of RJVs in the same SIC4 in which the firm is in in that year"

gen RJVdiff=RJVtotal-RJVsame
label var RJVdiff "Number of RJVs in a different SIC4 in which the firm is in in that year"

gen RJVsa=0
replace RJVsa=1 if RJVsame>0
label var RJVsa "Dummy =1 if the firm is at least in one RJV in same industry"

gen RJVdi=0
replace RJVdi=1 if RJVdiff>0
label var RJVdi "Dummy =1 if the firm is at least in one RJV in diff industry"


drop ins1-ins178

* total number of RJVs in the same/different SIC4 industry (definition based on all SIC codes)
foreach num of numlist 1/178 {
gen ins`num'=in`num'
replace ins`num'=. if rjv_sic`num'!=SICALL1 & rjv_sic`num'!=SICALL2 & rjv_sic`num'!=SICALL3 & rjv_sic`num'!=SICALL4 & rjv_sic`num'!=SICALL5 & rjv_sic`num'!=SICALL6 & rjv_sic`num'!=SICALL7 & rjv_sic`num'!=SICALL8 & rjv_sic`num'!=SICALL9 & rjv_sic`num'!=SICALL10 & rjv_sic`num'!=SICALL11 & rjv_sic`num'!=SICALL12 & rjv_sic`num'!=SICALL13 & rjv_sic`num'!=SICALL14 & rjv_sic`num'!=SICALL15
	}

egen RJVsameA=rowtotal(ins1-ins178)
label var RJVsameA "Number of RJVs in at least on of the SICcodes in which the firm is in in that year"

gen RJVdiffA=RJVtotal-RJVsameA
label var RJVdiffA "Number of RJVs in a different SIC4 than all firm'S SIC codes"

* total numer of firms met in the several RJVs
egen nRJV=rowtotal(nrjv1-nrjv178)
label var nRJV "Total number of firms in which the firm is in in that year"

*/


keep ticker year SIC RJV*
so ticker year

desc

save "RJVvars.dta", replace

log close
